﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Nmo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;
using Entity;

namespace DataControls
{
    public class LOPHOC_Data
    {
        DataBaseAccess dataBaseAccess = new DataBaseAccess();

        public void hienthi_LopHoc(DataGridView dtgLopHoc,String malophoc)
        {
            String excuteString = "SELECT * FROM LOPHOC WHERE MALOP='" + malophoc + "'";
            DataTable dataTable = new DataTable();
            dataTable = dataBaseAccess.getDataTable(excuteString);
            dtgLopHoc.DataSource = dataTable;
        }



        public void timkiemtheo_Lop(DataGridView dtgLopHoc, String tenlophoc)
        {
            String excuteString = "SELECT * FROM LOPHOC WHERE TENLOP like'%" + tenlophoc+ "'";
            DataTable dataTable = new DataTable();
            dataTable = dataBaseAccess.getDataTable(excuteString);
            dtgLopHoc.DataSource = dataTable;
        }

        public void timkiemtheo_GiaoVien(DataGridView dtgLopHoc, String tengiaovien)
        {
            String excuteString = "SELECT * FROM LOPHOC L, GIAOVIEN G WHERE L.MAGVCN = G.MAGV AND G.TENGV LIKE'%" + tengiaovien + "'";
            DataTable dataTable = new DataTable();
            dataTable = dataBaseAccess.getDataTable(excuteString);
            dtgLopHoc.DataSource = dataTable;
        }





        public void lophoc_Combobox(ComboBox cbLophoc)
        {
            String excuteString = "SELECT * FROM LOPHOC";
            dataBaseAccess.addto_Combobox(cbLophoc,excuteString);
        }

        public void lophoctheokhoa_Combobox(ComboBox cbLophoc, String makhoa)
        {
            String excuteString = "SELECT * FROM LOPHOC WHERE MAKHOA='"+ makhoa +"'";
            dataBaseAccess.addto_Combobox(cbLophoc, excuteString);
        }

        public void lophoc_GiaoVien(ComboBox cbLophoc,String magiaovien)
        {

            String excuteString = "SELECT * FROM LOPHOC WHERE MAGVCN='" + magiaovien + "'";
            dataBaseAccess.addto_Combobox(cbLophoc, excuteString);
        }


        public Boolean kiemtraMaLH_GiangDay(String malophoc)
        {
            int i = 0;
            String excuteString = "SELECT COUNT(*) FROM GIANGDAY WHERE MALOP='" + malophoc + "'";
            i = dataBaseAccess.Test(excuteString);
            if (i > 0)
                return true;
            else
                return false;
        }







        public Boolean kiemtra_MaLopHoc(String malophoc)
        {
            int i = 0;
            String excuteString = "SELECT COUNT(*) FROM LOPHOC WHERE MALOP='" + malophoc + "'";
            i = dataBaseAccess.Test(excuteString);
            if (i > 0)
                return true;
            else
                return false;
        }

        public Boolean kiemtra_TenLop(String tenlophoc)
        {
            int i = 0;
            String excuteString = "SElECT COUNT(*) FROM LOPHOC WHERE TENLOP='" + tenlophoc + "'";
            i = dataBaseAccess.Test(excuteString);
            if (i > 0)
                return true;
            else
                return false;
        }

        public void layten_HeDaoTao(String mahedatao, ComboBox cb)
        {
            String excuteString = "SELECT TENHDT FROM HEDAOTAO WHERE MAHDT='" + mahedatao + "'";
            String tenhedaotao = (String)dataBaseAccess.excuteScalar(excuteString);
            for (int i = 0; i <= cb.MaxLength; i++)
            {
                cb.SelectedIndex = i;
                if (cb.Text.ToString() == tenhedaotao)
                    break;
            }
        }

        public void layten_Khoa(String makhoa, ComboBox cb)
        {
            int a = 0;
            String excuteString = "SELECT TENKHOA FROM KHOA WHERE MAKHOA='" + makhoa + "'";
            String tenkhoa = (String)dataBaseAccess.excuteScalar(excuteString);
            for (int i = 0; i < cb.Items.Count; i++)
            {
                cb.SelectedIndex = i;
                if (cb.Text == tenkhoa)
                    a = i;
            }
            cb.SelectedIndex = a;
        }

        public void layten_GiaoVien(String magiaovien, ComboBox cb)
        {
            int a =0;
            String excuteString = "SELECT TENGV FROM GIAOVIEN WHERE MAGV='" + magiaovien + "'";
            String tengiaovien = (String)dataBaseAccess.excuteReader(excuteString);
            for (int i = 0; i < cb.Items.Count; i++)
            {
                cb.SelectedIndex = i;
                if (cb.Text == tengiaovien)
                    a = i;
            }
            cb.SelectedIndex = a;
        }

        public int solop_ChuNhiem(String magiaovien)
        {
            int solop = 0;
            
            String excuteString = "SELECT COUNT(*) FROM LOPHOC WHERE MAGVCN='" + magiaovien + "'";
            solop = Convert.ToInt16(dataBaseAccess.excuteScalar(excuteString));
            return solop;
        }

       
    }
}
